package com.example.demo.api;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.security.UserGroupInformation;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.io.IOException;
import java.security.PrivilegedAction;
import java.sql.*;
import java.util.logging.Logger;
/**
 * @author: MaLiang
 * @date: 2020/8/31 13:34
 * @version: 1.0
 **/
@RestController
@RequestMapping("api")
public class ApiController {
    Logger logger = Logger.getLogger(ApiController.class.getName());
    static String jdbc_driver = "com.cloudera.impala.jdbc41.Driver";
    /**
     *  Kerberos 认证的几个参数
     *  AuthMech： 0无认证、1Kerberos认证、2用户名方式、3用户名和密码认证、6使用Hadoop授权认证
     *  KrbRealm：你的KDC服务定义的域名
     *  krbHostFQDN：你的HiveServer2服务的FQDN（hostname或你dns解析的域名）
     *  KrbServiceName：HiveServer2服务的Principal默认为hive
     *  KrbAuthType：0表示获取你的Subject来实现Kerberos认证、1表示基于JAAS方式获取Kerberos认证、2表示基于当前客户端的Tick Cache方式认证
     *  */
    /**
     * 广丰内网测试地址
     * impala url
     * */
    //private static String conn_url = "jdbc:impala://192.168.10.111:21050/ods";

    private static String conn_url = "jdbc:impala://172.16.136.59:21050/ods;AuthMech=1;KrbHostFQDN=qfwa0468;KrbServiceName=impala;KrbRealm=GTMC.COM";
    //广丰生产地址

    private static String user = "dintm";
    private static String pwd = "pass1234";
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    ResultSetMetaData  rmd = null;
    ResultSet rs = null;
    String status = "==========数据写入失败==========";


    /*执行查询sql返回查询结果集*/
    @RequestMapping(value = "/getdata")
    public String QueryData(@RequestParam("sql") String sql) throws SQLException {
        logger.info("==========调用Kerberos认证返回数据库连接==========");
        // json数组用于解析返回数据
        JSONArray array = new JSONArray();
        try {
            logger.info("=====传入sql:"+sql);
            sql = sql.replaceAll("\"","");
            logger.info("=====替换后的sql："+sql);
            kerberosAuthenticationAndInitImpalaConnection();
            //getConnect();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            rmd = rs.getMetaData();
            int colCount = rmd.getColumnCount();
            //将返回结果集转为json 对象
            while (rs.next()){
                JSONObject jsonObj = new JSONObject();
                for(int i =1 ;i<=colCount;i++){
                    String col_name = rmd.getColumnLabel(i);
                    Object value = rs.getObject(col_name);
                    if(col_name.length()>=1){
                        jsonObj.put(col_name,value);
                    }
                }
                if(jsonObj.length()>=1){
                    array.put(jsonObj);

                }
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            ps.close();
            conn.close();
        }

        return array.toString();
    }

    /*执行插入sql 返回写入状态*/
    @RequestMapping(value = "/putdata")
    public String PutData(@RequestParam("sql") String sql) throws SQLException {
        try {
            /*Class.forName(jdbc_driver);
            conn = DriverManager.getConnection(conn_url);*/
            logger.info("==========调用Kerberos认证返回数据库连接==========");
            kerberosAuthenticationAndInitImpalaConnection();
            logger.info("=====传入sql:"+sql);
            sql = sql.replaceAll("\"","");
            logger.info("=====替换后的sql："+sql);
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
            status = "==========数据写入成功==========";
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            ps.close();
            conn.close();

        }
        return status;

    }

    /**
     * 得到impala连接
     * 获得impala JDBC连接，这边由于集成了Kerberos所以需要，在UserGroupInformation 信息里面进行连接初始化才能够验证权限通过
     * 不然会有该错出现： Unable to obtain Principal Name for authentication
     */
    public static void getConnect() throws SQLException {
        try {
            System.out.println("==========开始加载驱动===========");
            Class.forName(jdbc_driver);
            System.out.println("==========格式化驱动===========");

            conn =DriverManager.getConnection(conn_url,user,pwd);

            System.out.println("==========加载驱动完成===========");

        }
        catch (Exception e){
            e.printStackTrace();
            System.out.println("==========加载驱动出错啦===========");

        }

    }

    /**
     * 进行Kerberos认证，并且进行Impala JDBC连接权限初始化
     */
    public void kerberosAuthenticationAndInitImpalaConnection() throws IOException {
        //本地调试 获取文件路径
/*        String rootPath = System.getProperty("user.dir").toString()+"\\src\\main\\Krb5Conf\\";
        String krb5_conf=rootPath+"krb5.conf";
        String krb5_keytab = rootPath+"dintm_dev.keytab";*/
        //服务器部署获取的路径
        String rootPath = System.getProperty("user.dir").toString();
        System.out.println("=================================rootPath:"+rootPath);
        String krb5_conf=rootPath+"/krb5.conf";
        String krb5_keytab = rootPath+"/dintm_dev.keytab";
        String krb5_user = "dintm@GTMC.COM";
        Configuration conf = new Configuration();
        conf.set("hadoop.security.authentication", "Kerberos");
        System.setProperty("java.security.krb5.conf",krb5_conf);
        System.setProperty("java.security.krb5.realm","GTMC.COM");
        System.setProperty("java.security.krb5.kdc","qfwa0466");
        UserGroupInformation.setConfiguration(conf);
        UserGroupInformation.loginUserFromKeytab(krb5_user, krb5_keytab);
        UserGroupInformation loginUser = UserGroupInformation.getLoginUser();
        loginUser.doAs(new PrivilegedAction<Void>() {
            @Override
            public Void run(){
                try {
                    getConnect();
                }
                catch (SQLException e){
                    System.out.println("==========fail init Connection==========");
                    e.printStackTrace();
                }
                return null;
            }
        });

    }


}
